package com.conversationboard.batch.jobs.runstats;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import com.conversationboard.database.Database;
import com.conversationboard.database.Queries;
import com.conversationboard.logger.LogMessageType;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.BatchMessage;
import com.conversationboard.model.User;

public class RunstatsJob implements Job {

	public void execute(JobExecutionContext context) throws JobExecutionException {

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String status = "";

		try {

			connection = Database.getConnection();

			BatchMessage.setMessage("Database: Optimising Board Membership Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_BOARD_MEMBERSHIP);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Board Membership Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising Boards Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_BOARDS);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Boards Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising Messages Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_MESSAGES);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Messages Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising Polls Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_POLLS);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Polls Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising Threads Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_THREADS);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Threads Membership Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising User Roles Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_USERROLES);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "User Roles Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

			BatchMessage.setMessage("Database: Optimising Users Table", true);
			statement = connection.prepareStatement(Queries.RUNSTATS_USERS);
			statement.execute();
			resultSet = statement.getResultSet();
			resultSet.next();
			status = resultSet.getString("Msg_text");
			resultSet.close();
			Logger.log(new Date(), "Users Table Optimise Status: " + status, User.getAnonymousUser(), LogMessageType.SYSTEM);
			status = "";
			statement.close();

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} finally {

			BatchMessage.clearMessage();

			try {
				Database.close(connection);
			} catch (SQLException e) {
				throw new JobExecutionException(e);
			}
		}

	}

}
